//
//  YWWorkReminderDBManager.m
//  maintain
//
//  Created by Kyo on 16/8/31.
//  Copyright © 2016年 ewin. All rights reserved.
//

#import "YWWorkReminderDBManager.h"
#import "YWDBTool.h"
#import "YWInspectionMissionDBManager.h"
#import "YWInspectionEquipmentRecord.h"
#import "YWKeepWatchDBManager.h"

#import "YWMaterialDBManager.h"
#import "YWMaterialApply.h"
#import "YWMaterialReturn.h"
#import "YWMaterialStockOut.h"

#import "YWReminder.h"
#import "YWMeterReadingRecord.h"
#import "YWAttendanceRecord.h"
#import "YWReminderList.h"
#import "YWDailyReport.h"

@implementation YWWorkReminderDBManager
YWSingletonM(Manager)

- (FMDatabase*)fmdb{
    return [[YWDBTool shareDBTool] fmdb];
}
- (FMDatabaseQueue*)dbQeue{
    return [[YWDBTool shareDBTool] dbQeue];
}

- (void)dropWorkReminderTable{
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* dropWorkReminderTableSql = @"drop table if exists workReminder;";
        [db executeUpdate:dropWorkReminderTableSql];
    }];
}

//#pragma mark - 创建工作提醒表
//- (BOOL)createWorkReminderTable{
//    NSString* sql = @"CREATE TABLE IF NOT EXISTS 'workReminder' (reminderId text NOT NULL,title text,content text,toUserId integer,fromUserId integer,createTime timestamp,finishStatus smallint,isRead boolean DEFAULT 0,relationId text,relationType smallint);";
//    return [[self fmdb] executeUpdate:sql];
//}
//
//#pragma mark - 数据增删改查等
//- (BOOL)updateWorkReminder:(YWWorkReminder*)workReminder{
//    if (!workReminder) {
//        return YES;
//    }
//    __block BOOL executeResult = YES;
//    __block NSString* sql = nil;
//    [self.dbQeue inTransaction:^(FMDatabase* db, BOOL* rollback) {
//        sql = @"SELECT * FROM 'workReminder' WHERE reminderId=?;";
//        FMResultSet* set = [db executeQuery:sql, workReminder.reminderId];
//        if ([set next]) {
//            sql = @"UPDATE 'workReminder' SET title=?,content =?,toUserId =?,fromUserId =?,createTime =?,finishStatus =?,isRead =?,relationId =?,relationType=? WHERE reminderId=?;";
//            executeResult = [db executeUpdate:sql, workReminder.title, workReminder.content, @(workReminder.toUserId), @(workReminder.fromUserId), @(workReminder.createTime), @(workReminder.finishStatus), @(workReminder.isRead), workReminder.relationId, @(workReminder.relationType), workReminder.reminderId];
//        }
//        else {
//            sql = @"INSERT INTO 'workReminder' (reminderId,title,content,toUserId,fromUserId,createTime,finishStatus,isRead,relationId,relationType) VALUES (?, ?, ?, ?, ?,?, ?, ?, ?, ?);";
//            executeResult = [db executeUpdate:sql, workReminder.reminderId,workReminder.title, workReminder.content, @(workReminder.toUserId), @(workReminder.fromUserId), @(workReminder.createTime), @(workReminder.finishStatus), @(workReminder.isRead), workReminder.relationId, @(workReminder.relationType)];
//        }
//        if (!executeResult) {
//            *rollback = YES;
//        };
//        
//        [db closeOpenResultSets];
//    }];
//    return executeResult;
//}
//
//- (BOOL)updateWorkReminderWithArray:(NSArray*)workReminderArray{
//    if (workReminderArray.count == 0) {
//        return YES;
//    }
//    __block BOOL executeResult = YES;
//    __block NSArray* blockWorkReminderArray = workReminderArray;
//    __block NSString* sql = nil;
//    [self.dbQeue inTransaction:^(FMDatabase* db, BOOL* rollback) {
//        [blockWorkReminderArray enumerateObjectsUsingBlock:^(YWWorkReminder* _Nonnull workReminder, NSUInteger idx, BOOL* _Nonnull stop) {
//            sql = @"SELECT * FROM 'workReminder' WHERE reminderId=?;";
//            FMResultSet* set = [db executeQuery:sql, workReminder.reminderId];
//            if ([set next]) {
//                sql = @"UPDATE 'workReminder' SET title=?,content =?,toUserId =?,fromUserId =?,createTime =?,finishStatus =?,isRead =?,relationId =?,relationType=? WHERE reminderId=?;";
//                executeResult = [db executeUpdate:sql, workReminder.title, workReminder.content, @(workReminder.toUserId), @(workReminder.fromUserId), @(workReminder.createTime), @(workReminder.finishStatus), @(workReminder.isRead), workReminder.relationId, @(workReminder.relationType), workReminder.reminderId];
//            }
//            else {
//                sql = @"INSERT INTO 'workReminder' (reminderId,title,content,toUserId,fromUserId,createTime,finishStatus,isRead,relationId,relationType) VALUES (?, ?, ?, ?, ?,?, ?, ?, ?, ?);";
//                executeResult = [db executeUpdate:sql, workReminder.reminderId,workReminder.title, workReminder.content, @(workReminder.toUserId), @(workReminder.fromUserId), @(workReminder.createTime), @(workReminder.finishStatus), @(workReminder.isRead), workReminder.relationId, @(workReminder.relationType)];
//            }
//            if (!executeResult) {
//                *stop = YES;
//                *rollback = YES;
//            };
//        }];
//        [db closeOpenResultSets];
//    }];
//    return executeResult;
//}
//
//- (YWWorkReminder*)theLatestWorkReminder{
//    __block YWWorkReminder* reminder = nil;
//    [self.dbQeue inDatabase:^(FMDatabase* db) {
//        NSString* sql = @"SELECT * FROM 'workReminder' ORDER BY createTime desc LIMIT 1";
//        FMResultSet* set = [db executeQuery:sql];
//        if ([set next]) {
//            reminder = [self workReminderWithFMResultSet:set FMDatabase:db];
//        }
//        [db closeOpenResultSets];
//    }];
//    return reminder;
//}
//
//- (NSArray *)workReminderWithPage:(NSInteger)page{
//    __block NSMutableArray *workReminderArray = [NSMutableArray array];
//    [self.dbQeue inDatabase:^(FMDatabase* db) {
//        NSInteger showCount = page * 10;
//        NSString* sql = [NSString stringWithFormat:@"SELECT * FROM 'workReminder' ORDER BY createTime DESC LIMIT %ld;", (long)showCount];
//        FMResultSet* set = [db executeQuery:sql];
//        while ([set next]) {
//            YWWorkReminder* reminder = [self workReminderWithFMResultSet:set FMDatabase:db];
//            if (reminder) {
//                [workReminderArray addObject:reminder];
//            }
//        }
//        [db closeOpenResultSets];
//    }];
//    return workReminderArray;
//}
//
//- (YWWorkReminder*)workReminderWithFMResultSet:(FMResultSet*)set FMDatabase:(FMDatabase*)db{
//    YWWorkReminder* reminder = [[YWWorkReminder alloc] init];
//    reminder.reminderId = [set stringForColumn:@"reminderId"];
//    reminder.title = [set stringForColumn:@"title"];
//    reminder.content = [set stringForColumn:@"content"];
//    reminder.toUserId = [set intForColumn:@"toUserId"];
//    NSString* toUsersql = @"SELECT * FROM 'handler' WHERE uniqueId =?;";
//    FMResultSet* toUserSet = [db executeQuery:toUsersql, @(reminder.toUserId)];
//    if ([toUserSet next]) {
//        reminder.toUser = [[YWDBTool shareDBTool] handlerWithSet:toUserSet];
//    }
//    reminder.fromUserId = [set intForColumn:@"fromUserId"];
//    NSString* fromUsersql = @"SELECT * FROM 'handler' WHERE uniqueId =?;";
//    FMResultSet* fromUserSet = [db executeQuery:fromUsersql, @(reminder.fromUserId)];
//    if ([fromUserSet next]) {
//        reminder.fromUser = [[YWDBTool shareDBTool] handlerWithSet:fromUserSet];
//    }
//    reminder.createTime = [set longLongIntForColumn:@"createTime"];
//    reminder.finishStatus = [set intForColumn:@"finishStatus"];
//    reminder.isRead = [set boolForColumn:@"isRead"];
//    reminder.relationId = [set stringForColumn:@"relationId"];
//    reminder.relationType = [set intForColumn:@"relationType"];
//    if (reminder.relationType >= Trouble_Report && reminder.relationType <= Trouble_Mission) {
//        NSString* sql = @"SELECT * FROM 'mission_trouble' WHERE troubleId = ?";
//        FMResultSet* troubleSet = [db executeQuery:sql, @([reminder.relationId integerValue])];
//        if ([troubleSet next]) {
//            YWTroubleMission *troubleMission = [[YWDBTool shareDBTool] troubleMissionWithFMResultSet:troubleSet FMDatabase:db];
//            reminder.object = troubleMission;
//        }
//    }else if (reminder.relationType >= Detection_Mission_Assign && reminder.relationType <= Detection_Mission_Done){
//        NSString* sql = @"SELECT * FROM 'mission_maintenance' WHERE missionId = ?";
//        FMResultSet* set = [db executeQuery:sql, @([reminder.relationId integerValue])];
//        if ([set next]) {
//            YWMaintenanceMission *mission = [YWDetectAndUpkeepDBManager maintenanceMissionWithFMResultSet:set FMDatabase:db];
//            reminder.object = mission;
//        }
//    }else if (reminder.relationType >= Upkeep_Mission_Assign && reminder.relationType <= Upkeep_Mission_Done){
//        NSString* sql = @"SELECT * FROM 'mission_maintenance' WHERE missionId = ?";
//        FMResultSet* set = [db executeQuery:sql, @([reminder.relationId integerValue])];
//        if ([set next]) {
//            YWMaintenanceMission *mission = [YWDetectAndUpkeepDBManager maintenanceMissionWithFMResultSet:set FMDatabase:db];
//            reminder.object = mission;
//        }
//    }else if (reminder.relationType >= Inspection_Mission_Assign && reminder.relationType <= Inspection_Mission_Modify){
//        NSString* sql = @"SELECT * FROM 'inspectionMission' WHERE inspectionMissionId= ?";
//        FMResultSet* set = [db executeQuery:sql,@([reminder.relationId integerValue])];
//        if ([set next]) {
//            YWInspectionMissionModel *inspectionMission = [[YWInspectionMissionDBManager shareManager] inspectionMissionWithFMResultSet:set FMDatabase:db];
//            reminder.object = inspectionMission;
//        }
//    }else if (reminder.relationType >= KeepWatch_Mission_Assign && reminder.relationType <= KeepWatch_Mission_Modify){
//        NSString* sql = @"SELECT * FROM 'keepwatchmission' WHERE keepWatchMissionId = ?";
//        FMResultSet* set = [db executeQuery:sql,@([reminder.relationId integerValue])];
//        if ([set next]) {
//            YWKeepWatchMission *keepWatchMission = [[YWKeepWatchDBManager shareManager] keepWatchMissionWithFMResultSet:set FMDatabase:db];
//            reminder.object = keepWatchMission;
//        }
//    }else if (reminder.relationType >= Material_Apply && reminder.relationType <= Material_Apply_Return){
//        if (reminder.relationType == Material_Apply_StockOut) {
//            NSString* sql = @"SELECT * FROM 'materialReceipient' WHERE stockOutId = ? AND stockOutStatus =1;";
//            FMResultSet* set = [db executeQuery:sql, @([reminder.relationId integerValue])];
//            if ([set next]) {
//                YWMaterialStockOut *materialReceipient = [[YWMaterialDBManager shareManager] materialReceipientFMResultSet:set FMDatabase:db];
//                reminder.object = materialReceipient;
//            }
//            
//        }else if (reminder.relationType == Material_Apply_Return){
//            NSString * sql = @"SELECT * FROM 'materialReturn' WHERE returnId = ? AND returnStatus=2;";
//            FMResultSet* set = [db executeQuery:sql, @([reminder.relationId integerValue])];
//            if ([set next]) {
//                YWMaterialReturn *materialReturn = [[YWMaterialDBManager shareManager] materialReturnFMResultSet:set FMDatabase:db];
//                reminder.object = materialReturn;
//            }
//        }else{
//            NSString* sql = @"SELECT * FROM 'materialApply' WHERE applyId = ?;";
//            // AND applyStatus=0
//            FMResultSet* set = [db executeQuery:sql, @([reminder.relationId integerValue])];
//            if ([set next]) {
//                YWMaterialApplyViewModel *applyViewModel = [[YWMaterialDBManager shareManager] materialApplyWithFMResultSet:set FMDatabase:db];
//                reminder.object = applyViewModel;
//            }
//        }
//    }
//    return reminder.object?reminder:nil;
//}
//
//- (BOOL)updateReadStatusWithWorkReminder:(YWWorkReminder*)workReminder{
//    __block BOOL executeResult = YES;
//    [self.dbQeue inDatabase:^(FMDatabase* db) {
//        NSString* sql = @"UPDATE 'workReminder' SET isRead=? WHERE reminderId=?;";
//        executeResult = [db executeUpdate:sql, @(YES),workReminder.reminderId];
//        [db closeOpenResultSets];
//    }];
//    return executeResult;
//}

#pragma mark - 创建提醒表
- (BOOL)createReminderTable{
    NSString* sql = @"CREATE TABLE IF NOT EXISTS 'reminder' (reminderId integer NOT NULL UNIQUE,title varchar,content varchar,createTime timestamp,relationId integer,type smallint,linkUrl varchar,missionSequence varchar,missionType smallint,missionTitle varchar,patrolLineName varchar,expDate timestamp,missionStartTime timestamp,missionEndTime timestamp,todoDesignTroubleCount integet,todoKeepWatchMissionCount integer,todoInspectionMissionCount integer,todoDetectionCount integer,todoUpkeepCount integer,todoFixTroubleCount integer,finishKeepWatchCount integer,finishInspectionCount integer,finishDetectionCount integer,finishUpkeepCount integer,finishFixTroubleCount integer,versionCode integer,versionName varchar,versionDescription varchar,publishDate timestamp,isRead boolean DEFAULT 0,operationType smallint);";
    return [[self fmdb] executeUpdate:sql];
}

- (BOOL)updateReminder:(YWReminder*)reminder{
    __block BOOL executeResult = YES;
    [self.dbQeue inTransaction:^(FMDatabase* db, BOOL* rollback) {
        NSString* sql = @"SELECT * FROM 'reminder' WHERE reminderId=?;";
        FMResultSet* set = [db executeQuery:sql, @(reminder.reminderId)];
        if ([set next]) {
            sql = @"UPDATE 'reminder' SET title =?,content =?,createTime =?,relationId =?,type =?,linkUrl =?,missionSequence =?,missionType =?,missionTitle =?, patrolLineName =?,expDate =?,missionStartTime =?,missionEndTime =?,todoDesignTroubleCount =?,todoKeepWatchMissionCount =?,todoInspectionMissionCount =?,todoDetectionCount =?,todoUpkeepCount =?,todoFixTroubleCount =?,finishKeepWatchCount =?,finishInspectionCount =?,finishDetectionCount =?,finishUpkeepCount =?,finishFixTroubleCount =?,versionCode =?,versionName =?,versionDescription =?,publishDate =?,operationType=? WHERE reminderId=?;";
            executeResult = [db executeUpdate:sql, reminder.title, reminder.content, @(reminder.createTime), @(reminder.relationId), @(reminder.type), reminder.linkUrl,reminder.missionSequence,@(reminder.missionType),reminder.missionTitle,reminder.patrolLineName,@(reminder.expDate),@(reminder.missionStartTime),@(reminder.missionEndTime),@(reminder.todoDesignTroubleCount),@(reminder.todoKeepWatchMissionCount),@(reminder.todoInspectionMissionCount),@(reminder.todoDetectionCount),@(reminder.todoUpkeepCount),@(reminder.todoFixTroubleCount),@(reminder.finishKeepWatchCount),@(reminder.finishInspectionCount),@(reminder.finishDetectionCount),@(reminder.finishUpkeepCount),@(reminder.finishFixTroubleCount),@(reminder.versionCode),reminder.versionName,reminder.versionDescription,@(reminder.publishDate),@(reminder.operationType),@(reminder.reminderId)];
        }
        else {
            sql = @"INSERT INTO 'reminder' (reminderId,title,content,createTime,relationId,type,linkUrl,missionSequence,missionType,missionTitle,patrolLineName,expDate,missionStartTime,missionEndTime,todoDesignTroubleCount,todoKeepWatchMissionCount,todoInspectionMissionCount,todoDetectionCount,todoUpkeepCount,todoFixTroubleCount,finishKeepWatchCount,finishInspectionCount,finishDetectionCount,finishUpkeepCount,finishFixTroubleCount,versionCode,versionName,versionDescription,publishDate,operationType) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
            executeResult = [db executeUpdate:sql, @(reminder.reminderId),reminder.title, reminder.content, @(reminder.createTime), @(reminder.relationId), @(reminder.type), reminder.linkUrl,reminder.missionSequence,@(reminder.missionType),reminder.missionTitle,reminder.patrolLineName,@(reminder.expDate),@(reminder.missionStartTime),@(reminder.missionEndTime),@(reminder.todoDesignTroubleCount),@(reminder.todoKeepWatchMissionCount),@(reminder.todoInspectionMissionCount),@(reminder.todoDetectionCount),@(reminder.todoUpkeepCount),@(reminder.todoFixTroubleCount),@(reminder.finishKeepWatchCount),@(reminder.finishInspectionCount),@(reminder.finishDetectionCount),@(reminder.finishUpkeepCount),@(reminder.finishFixTroubleCount),@(reminder.versionCode),reminder.versionName,reminder.versionDescription,@(reminder.publishDate),@(reminder.operationType)];
        }
        if (!executeResult) {
            *rollback = YES;
        };
        
        [db closeOpenResultSets];
    }];
    return executeResult;
}

- (BOOL)updateReminderWithArray:(NSArray*)reminderArray{
    if (reminderArray.count == 0) {
        return YES;
    }
    __block BOOL executeResult = YES;
    [self.dbQeue inTransaction:^(FMDatabase* db, BOOL* rollback) {
        [reminderArray enumerateObjectsUsingBlock:^(YWReminder* _Nonnull reminder, NSUInteger idx, BOOL* _Nonnull stop) {
            NSString* sql = @"SELECT * FROM 'reminder' WHERE reminderId=?;";
            FMResultSet* set = [db executeQuery:sql, @(reminder.reminderId)];
            if ([set next]) {
                sql = @"UPDATE 'reminder' SET title =?,content =?,createTime =?,relationId =?,type =?,linkUrl =?,missionSequence =?,missionType =?,missionTitle=?, patrolLineName=?,expDate =?,missionStartTime =?,missionEndTime =?,todoDesignTroubleCount =?,todoKeepWatchMissionCount =?,todoInspectionMissionCount =?,todoDetectionCount =?,todoUpkeepCount =?,todoFixTroubleCount =?,finishKeepWatchCount =?,finishInspectionCount =?,finishDetectionCount =?,finishUpkeepCount =?,finishFixTroubleCount =?,versionCode =?,versionName =?,versionDescription =?,publishDate =?,operationType=? WHERE reminderId=?;";
                executeResult = [db executeUpdate:sql, reminder.title, reminder.content, @(reminder.createTime), @(reminder.relationId), @(reminder.type), reminder.linkUrl,reminder.missionSequence,@(reminder.missionType),reminder.missionTitle,reminder.patrolLineName,@(reminder.expDate),@(reminder.missionStartTime),@(reminder.missionEndTime),@(reminder.todoDesignTroubleCount),@(reminder.todoKeepWatchMissionCount),@(reminder.todoInspectionMissionCount),@(reminder.todoDetectionCount),@(reminder.todoUpkeepCount),@(reminder.todoFixTroubleCount),@(reminder.finishKeepWatchCount),@(reminder.finishInspectionCount),@(reminder.finishDetectionCount),@(reminder.finishUpkeepCount),@(reminder.finishFixTroubleCount),@(reminder.versionCode),reminder.versionName,reminder.versionDescription,@(reminder.publishDate),@(reminder.operationType),@(reminder.reminderId)];
            }
            else {
                sql = @"INSERT INTO 'reminder' (reminderId,title,content,createTime,relationId,type,linkUrl,missionSequence,missionType,missionTitle,patrolLineName,expDate,missionStartTime,missionEndTime,todoDesignTroubleCount,todoKeepWatchMissionCount,todoInspectionMissionCount,todoDetectionCount,todoUpkeepCount,todoFixTroubleCount,finishKeepWatchCount,finishInspectionCount,finishDetectionCount,finishUpkeepCount,finishFixTroubleCount,versionCode,versionName,versionDescription,publishDate,operationType) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
                executeResult = [db executeUpdate:sql, @(reminder.reminderId),reminder.title, reminder.content, @(reminder.createTime), @(reminder.relationId), @(reminder.type), reminder.linkUrl,reminder.missionSequence,@(reminder.missionType),reminder.missionTitle,reminder.patrolLineName,@(reminder.expDate),@(reminder.missionStartTime),@(reminder.missionEndTime),@(reminder.todoDesignTroubleCount),@(reminder.todoKeepWatchMissionCount),@(reminder.todoInspectionMissionCount),@(reminder.todoDetectionCount),@(reminder.todoUpkeepCount),@(reminder.todoFixTroubleCount),@(reminder.finishKeepWatchCount),@(reminder.finishInspectionCount),@(reminder.finishDetectionCount),@(reminder.finishUpkeepCount),@(reminder.finishFixTroubleCount),@(reminder.versionCode),reminder.versionName,reminder.versionDescription,@(reminder.publishDate),@(reminder.operationType)];
            }
            if (!executeResult) {
                *stop = YES;
                *rollback = YES;
            };
        }];
        [db closeOpenResultSets];
    }];
    return executeResult;
}

- (NSArray *)reminderWithPage:(NSInteger)page{
    __block NSMutableArray *reminderArray = [NSMutableArray array];
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSInteger showCount = page * 10;
        NSString* sql = [NSString stringWithFormat:@"SELECT * FROM 'reminder' ORDER BY createTime DESC LIMIT %ld;", (long)showCount];
        FMResultSet* set = [db executeQuery:sql];
        while ([set next]) {
            YWReminder* reminder = [self reminderWithFMResultSet:set FMDatabase:db];
            [reminderArray addObject:reminder];
        }
        [db closeOpenResultSets];
    }];
    return reminderArray;
}

- (NSArray *)reminderWithType:(NSInteger)reminderType page:(NSInteger)page{
    __block NSMutableArray *reminderArray = [NSMutableArray array];
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSInteger showCount = page * 10;
        NSString* sql = [NSString stringWithFormat:@"SELECT * FROM 'reminder' WHERE type=%ld ORDER BY createTime DESC LIMIT %ld;", (long)reminderType,(long)showCount];
        FMResultSet* set = [db executeQuery:sql];
        while ([set next]) {
            YWReminder* reminder = [self reminderWithFMResultSet:set FMDatabase:db];
            [reminderArray addObject:reminder];
        }
        [db closeOpenResultSets];
    }];
    return reminderArray;
}

- (YWReminder*)reminderWithFMResultSet:(FMResultSet*)set FMDatabase:(FMDatabase*)db{
    YWReminder* reminder = [[YWReminder alloc] init];
    reminder.reminderId = [set intForColumn:@"reminderId"];
    reminder.title = [set stringForColumn:@"title"];
    reminder.content = [set stringForColumn:@"content"];
    reminder.createTime = [set longLongIntForColumn:@"createTime"];
    reminder.relationId = [set intForColumn:@"relationId"];
    reminder.type = [set intForColumn:@"type"];
    reminder.linkUrl = [set stringForColumn:@"linkUrl"];
    reminder.missionSequence = [set stringForColumn:@"missionSequence"];
    reminder.missionType = [set intForColumn:@"missionType"];
    reminder.missionTitle = [set stringForColumn:@"missionTitle"];
    reminder.patrolLineName = [set stringForColumn:@"patrolLineName"];
    reminder.expDate = [set longLongIntForColumn:@"expDate"];
    reminder.missionStartTime = [set longLongIntForColumn:@"missionStartTime"];
    reminder.missionEndTime = [set longLongIntForColumn:@"missionEndTime"];
    reminder.todoDesignTroubleCount = [set intForColumn:@"todoDesignTroubleCount"];
    reminder.todoKeepWatchMissionCount = [set intForColumn:@"todoKeepWatchMissionCount"];
    reminder.todoInspectionMissionCount = [set intForColumn:@"todoInspectionMissionCount"];
    reminder.todoDetectionCount = [set intForColumn:@"todoDetectionCount"];
    reminder.todoUpkeepCount = [set intForColumn:@"todoUpkeepCount"];
    reminder.todoFixTroubleCount = [set intForColumn:@"todoFixTroubleCount"];
    reminder.finishKeepWatchCount = [set intForColumn:@"finishKeepWatchCount"];
    reminder.finishInspectionCount = [set intForColumn:@"finishInspectionCount"];
    reminder.finishDetectionCount = [set intForColumn:@"finishDetectionCount"];
    reminder.finishUpkeepCount = [set intForColumn:@"finishUpkeepCount"];
    reminder.finishFixTroubleCount = [set intForColumn:@"finishFixTroubleCount"];
    reminder.versionCode = [set intForColumn:@"versionCode"];
    reminder.versionName = [set stringForColumn:@"versionName"];
    reminder.versionDescription = [set stringForColumn:@"versionDescription"];
    reminder.publishDate = [set longLongIntForColumn:@"publishDate"];
    reminder.operationType = [set intForColumn:@"operationType"];
    return reminder;
}

- (BOOL)existUploadFailedMissions{
    __block NSInteger uploadFailedCount = 0;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = nil;
        FMResultSet* set = nil;
        if (uploadFailedCount==0) {
            sql = [NSString stringWithFormat:@"SELECT COUNT(*) uploadFailedCount FROM 'YW_ReportTrouble' WHERE uploadFail = 1 AND troubleStatus=%lu",(unsigned long)YWTroubleMissionStatusUnAssign];
            set = [db executeQuery:sql];
            if ([set next]) {
                uploadFailedCount = [set longForColumn:@"uploadFailedCount"];
            }
        }
        if (uploadFailedCount==0) {
            sql = [NSString stringWithFormat:@"SELECT COUNT(*) uploadFailedCount FROM 'YW_TroubleMission' WHERE uploadFail = 1 AND troubleStatus=%lu",(unsigned long)YWTroubleMissionStatusUnAssign];
            set = [db executeQuery:sql];
            if ([set next]) {
                uploadFailedCount = [set longForColumn:@"uploadFailedCount"];
            }
        }
        if (uploadFailedCount==0) {
            sql = @"SELECT COUNT(*) uploadFailedCount FROM 'patrolpoint_record' WHERE uploadStatus=? or uploadStatus=? order by uniqueTag,patrolTime desc";
            set = [db executeQuery:sql, @(YWPatrolPointRecordUploadStatusUnFinished),@(YWPatrolPointRecordUploadStatusUploading)];
            if ([set next]) {
                uploadFailedCount = [set longForColumn:@"uploadFailedCount"];
            }
        }
        if (uploadFailedCount==0) {
            sql = @"SELECT COUNT(*) uploadFailedCount FROM 'inspectionEquipment_record' WHERE (uploadStatus=? or uploadStatus=?)";
            set = [db executeQuery:sql, @(YWInspectionEquipmentRecordUploadStatusUnFinished),@(YWInspectionEquipmentRecordUploadStatusUploading)];
            if ([set next]) {
                uploadFailedCount = [set longForColumn:@"uploadFailedCount"];
            }
        }
        if (uploadFailedCount==0) {
            sql = @"SELECT COUNT(*) uploadFailedCount FROM 'equipment_trouble_record' WHERE (uploadStatus=? or uploadStatus=?) ORDER BY troubleId;";
            set = [db executeQuery:sql, @(YWEquipmentMaintenanceRecordUploadStatusUnFinished),@(YWEquipmentMaintenanceRecordUploadStatusUploading)];
            if ([set next]) {
                uploadFailedCount = [set longForColumn:@"uploadFailedCount"];
            }
        }
        if (uploadFailedCount==0) {
            sql = @"SELECT COUNT(*) uploadFailedCount FROM 'equipment_detection_record' WHERE (uploadStatus=? or uploadStatus=?) ORDER BY missionId;";
            set = [db executeQuery:sql, @(YWEquipmentMaintenanceRecordUploadStatusUnFinished),@(YWEquipmentMaintenanceRecordUploadStatusUploading)];
            if ([set next]) {
                uploadFailedCount = [set longForColumn:@"uploadFailedCount"];
            }
        }
        if (uploadFailedCount==0) {
            sql = @"SELECT COUNT(*) uploadFailedCount FROM 'equipment_upkeep_record' WHERE (uploadStatus=? or uploadStatus=?) ORDER BY missionId;";
            set = [db executeQuery:sql, @(YWEquipmentMaintenanceRecordUploadStatusUnFinished),@(YWEquipmentMaintenanceRecordUploadStatusUploading)];
            if ([set next]) {
                uploadFailedCount = [set longForColumn:@"uploadFailedCount"];
            }
        }
        if (uploadFailedCount==0) {
            sql = @"SELECT COUNT(*) uploadFailedCount FROM 'meter_reading_record' WHERE uploadStatus=? or uploadStatus=?";
            set = [db executeQuery:sql, @(YWMeterReadingRecordUploadStatusUnFinished),@(YWMeterReadingRecordUploadStatusUploading)];
            if ([set next]) {
                uploadFailedCount = [set longForColumn:@"uploadFailedCount"];
            }
        }
        if (uploadFailedCount==0) {
            sql = @"SELECT COUNT(*) uploadFailedCount FROM 'attendance_record' WHERE uploadStatus=? or uploadStatus=?";
            set = [db executeQuery:sql, @(YWAttendanceUploadStatusFailure),@(YWAttendanceUploadStatusUploading)];
            if ([set next]) {
                uploadFailedCount = [set longForColumn:@"uploadFailedCount"];
            }
        }
        [db closeOpenResultSets];
    }];
    return uploadFailedCount >0 ? YES : NO;
}

- (BOOL)updateReminderIsReadStatus{
    __block BOOL executeResult = YES;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"UPDATE 'reminder' SET isRead=?";
        executeResult = [db executeUpdate:sql, @(YES)];
        [db closeOpenResultSets];
    }];
    return executeResult;
}

- (NSInteger)countOfUnreadReminder{
    __block NSInteger unreadCount = 0;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString *sql = @"SELECT COUNT(*) unreadCount FROM 'reminder' WHERE isRead = 0";
        FMResultSet* set = [db executeQuery:sql];
        if ([set next]) {
            unreadCount = [set longForColumn:@"unreadCount"];
        }
        [db closeOpenResultSets];
    }];
    return unreadCount;
}

#pragma mark - remind totalCount table
- (BOOL)createReminderCountTable{
    //-----------提醒总数和上一次的总数
    NSString* sql = @"CREATE TABLE IF NOT EXISTS 'reminderCount' (reminderType integer NOT NULL UNIQUE,totalCount integer,lastTotalCount integer);";
    BOOL success = [[self fmdb] executeUpdate:sql];
    if (success) {
        [self.reminderTypeArray enumerateObjectsUsingBlock:^(id  _Nonnull obj, NSUInteger idx, BOOL * _Nonnull stop) {
            NSString *sql = @"INSERT INTO 'reminderCount' (reminderType,totalCount,lastTotalCount) VALUES (?,?,?);";
            [[self fmdb] executeUpdate:sql,@([obj integerValue]),@(0),@(0)];
        }];
    }
    return success;
}

- (NSArray*)reminderTypeArray{
    return @[@(YWReminderType_missionExpiration),@(YWReminderType_missionChange),@(YWReminderType_missionCancel),@(YWReminderType_keepWatchNoBody),@(YWReminderType_inspectionNoBody),@(YWReminderType_dailyRport),@(YWReminderType_systemNotice),@(YWReminderType_appVersionNotice)];
}

- (BOOL)updateReminderTotalCount:(YWReminderList*)reminderList{
    if (reminderList == nil) {
        return YES;
    }
    __block BOOL executeResult = YES;
    [self.dbQeue inTransaction:^(FMDatabase* db, BOOL* rollback) {
        [self.reminderTypeArray enumerateObjectsUsingBlock:^(id  _Nonnull obj, NSUInteger idx, BOOL * _Nonnull stop) {
            NSInteger reminderType = [obj integerValue];
            NSString *sql = [NSString stringWithFormat:@"UPDATE 'reminderCount' SET totalCount=? where reminderType=%ld",(long)reminderType];
            if (reminderType == YWReminderType_missionExpiration) {
                executeResult = [db executeUpdate:sql, @(reminderList.remindMissionExpCount)];
            }else if (reminderType == YWReminderType_missionChange){
                executeResult = [db executeUpdate:sql, @(reminderList.remindMissionChangeCount)];
            }else if (reminderType == YWReminderType_missionCancel){
                executeResult = [db executeUpdate:sql, @(reminderList.remindMissionCancelCount)];
            }else if (reminderType == YWReminderType_keepWatchNoBody){
                executeResult = [db executeUpdate:sql, @(reminderList.remindKeepWatchNoBodyCount)];
            }else if (reminderType == YWReminderType_inspectionNoBody){
                executeResult = [db executeUpdate:sql, @(reminderList.remindInspectionNoBodyCount)];
            }else if (reminderType == YWReminderType_dailyRport){
                executeResult = [db executeUpdate:sql, @(reminderList.remindDayReportCount)];
            }else if (reminderType == YWReminderType_systemNotice){
                executeResult = [db executeUpdate:sql, @(reminderList.remindSystemNoticeCount)];
            }else if (reminderType == YWReminderType_appVersionNotice){
                executeResult = [db executeUpdate:sql, @(reminderList.remindAppVersionNoticeCount)];
            }
        }];
        if (!executeResult) {
            *rollback = YES;
        };
        [db closeOpenResultSets];
    }];
    return executeResult;
}

- (BOOL)updateReminderLastTotalCountWithType:(NSInteger)reminderType{
    if (reminderType<=0) {
        return YES;
    }
    __block BOOL executeResult = YES;
    [self.dbQeue inTransaction:^(FMDatabase* db, BOOL* rollback) {
        NSString *sql = [NSString stringWithFormat:@"SELECT totalCount FROM 'reminderCount' WHERE reminderType=%ld",(long)reminderType];
        FMResultSet *set = [db executeQuery:sql];
        NSInteger totalCount = 0;
        if ([set next]) {
            totalCount = [set intForColumn:@"totalCount"];
        }
        if (totalCount>=0) {
            sql = @"UPDATE 'reminderCount' SET lastTotalCount=? where reminderType=?";
            executeResult = [db executeUpdate:sql, @(totalCount),@(reminderType)];
        }
        if (!executeResult) {
            *rollback = YES;
        };
        [db closeOpenResultSets];
    }];
    return executeResult;
}

- (NSArray *)reminderListDateArray{
    __block NSMutableArray *reminderArray = [NSMutableArray array];
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSArray *typeArray = [self reminderTypeArray];
        [typeArray enumerateObjectsUsingBlock:^(id  _Nonnull obj, NSUInteger idx, BOOL * _Nonnull stop) {
            NSInteger reminderType = [obj integerValue];
            NSString *sql = [NSString stringWithFormat:@"SELECT * FROM 'reminder' WHERE type=%ld ORDER BY createTime DESC LIMIT 1;", (long)reminderType];
            FMResultSet *set = [db executeQuery:sql];
            if ([set next]) {
                NSString *newsSql = [NSString stringWithFormat:@"SELECT * FROM 'reminderCount' WHERE reminderType=%ld",(long)reminderType];
                FMResultSet *newset = [db executeQuery:newsSql];
                NSInteger unreadCount = 0;
                if ([newset next]) {
                    NSInteger totalCount = [newset intForColumn:@"totalCount"];
                    NSInteger lastTotalCount = [newset intForColumn:@"lastTotalCount"];
                    unreadCount = totalCount - lastTotalCount;
                }
                YWReminder *reminder = [self reminderWithFMResultSet:set FMDatabase:db];
                reminder.unreadCount = unreadCount>0?unreadCount:0;
                [reminderArray addObject:reminder];
            }
        }];
        [db closeOpenResultSets];
    }];
    NSSortDescriptor *sortDescriptor = [[NSSortDescriptor alloc] initWithKey:@"createTime" ascending:NO];
    NSArray *sortArray = [reminderArray sortedArrayUsingDescriptors:[NSArray arrayWithObject:sortDescriptor]];
    return sortArray;
}

- (NSInteger)unreadRemindTotalCount{
    __block NSInteger unreadRemindTotalCount = 0;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString *sql = @"SELECT * FROM 'reminderCount'";
        FMResultSet *set = [db executeQuery:sql];
        while ([set next]) {
            NSInteger totalCount = [set intForColumn:@"totalCount"];
            NSInteger lastTotalCount = [set intForColumn:@"lastTotalCount"];
            NSInteger unreadCount = totalCount - lastTotalCount;
            unreadRemindTotalCount = unreadCount + unreadRemindTotalCount;
        }
        [db closeOpenResultSets];
    }];
    return unreadRemindTotalCount>0 ? unreadRemindTotalCount : 0;
}

//- (YWReminderList *)reminderCount{
//    __block YWReminderList *reminderList = nil;
//    [self.dbQeue inDatabase:^(FMDatabase* db) {
//        NSString *sql = @"SELECT * FROM 'reminderCount'";
//        FMResultSet* set = [db executeQuery:sql];
//        if ([set next]) {
//            reminderList = [self reminderCountWithFMResultSet:set FMDatabase:db];
//        }
//        [db closeOpenResultSets];
//    }];
//    return reminderList;
//}
//
//- (YWReminderList*)reminderCountWithFMResultSet:(FMResultSet*)set FMDatabase:(FMDatabase*)db{
//    YWReminderList* reminderList = [[YWReminderList alloc] init];;
//    reminderList.remindMissionExpCount = [set intForColumn:@"remindMissionExpCount"];
//    reminderList.remindMissionChangeCount = [set intForColumn:@"remindMissionChangeCount"];
//    reminderList.remindMissionCancelCount = [set intForColumn:@"remindMissionCancelCount"];
//    reminderList.remindKeepWatchNoBodyCount = [set intForColumn:@"remindKeepWatchNoBodyCount"];
//    reminderList.remindInspectionNoBodyCount = [set intForColumn:@"remindInspectionNoBodyCount"];
//    reminderList.remindDayReportCount = [set intForColumn:@"remindDayReportCount"];
//    reminderList.remindSystemNoticeCount = [set intForColumn:@"remindSystemNoticeCount"];
//    reminderList.remindAppVersionNoticeCount = [set intForColumn:@"remindAppVersionNoticeCount"];
//    return reminderList;
//}

- (BOOL)createDayReportTable{
    NSString* sql = @"CREATE TABLE IF NOT EXISTS 'dayReport' (buildingId varchar,analysisDate varchar,analysisDateTime timestamp,total integer,unFinishCount integer,finishCount integer,tempCount integer,unDistributeCount integer,maintenanceTypeId integer);";
    return [[self fmdb] executeUpdate:sql];
}

- (BOOL)updateDailyReportWithDailyArray:(NSArray*)dailyArray{
    if (dailyArray.count == 0) {
        return YES;
    }
    __block BOOL executeResult = YES;
    __block NSArray* blockDailyArray = dailyArray;
    __block NSString* sql = nil;
    [self.dbQeue inTransaction:^(FMDatabase* db, BOOL* rollback) {
        YWDailyReport * daily = blockDailyArray[0];
        sql = @"DELETE FROM 'dayReport' WHERE analysisDate=?;";
        executeResult = [db executeUpdate:sql,daily.analysisDate];
        [blockDailyArray enumerateObjectsUsingBlock:^(YWDailyReport* _Nonnull dailyReport, NSUInteger idx, BOOL* _Nonnull stop) {
            if (executeResult) {
                sql = @"INSERT INTO 'dayReport' (buildingId,analysisDate,analysisDateTime,total,unFinishCount,finishCount,tempCount,unDistributeCount,maintenanceTypeId) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);";
                executeResult = [db executeUpdate:sql,dailyReport.buildingId,dailyReport.analysisDate,@(dailyReport.analysisDateTime),@(dailyReport.total),@(dailyReport.unFinishCount),@(dailyReport.finishCount),@(dailyReport.tempCount),@(dailyReport.unDistributeCount),@(dailyReport.maintenanceTypeId)];
            }
            if (!executeResult) {
                *stop = YES;
                *rollback = YES;
            };
        }];
        [db closeOpenResultSets];
    }];
    return executeResult;
}

- (NSMutableDictionary*)dailyReportArrayWithDailyDate:(NSString*)analysisDate{
    __block NSMutableArray *buildingDailyArray = [NSMutableArray array];
    __block NSMutableDictionary *dailyDic = [NSMutableDictionary dictionary];
    __block NSMutableArray *buildingIds = [NSMutableArray array];
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = nil;
        FMResultSet* set = nil;
        sql = [NSString stringWithFormat:@"select distinct buildingId from 'dayReport' where analysisDate = ?"];
        set = [db executeQuery:sql, analysisDate];
        while ([set next]) {
            NSString * buildingId = [set stringForColumn:@"buildingId"];
            [buildingIds addObject:buildingId];
        }
        //查询buildingId
        [dailyDic setObject:buildingIds forKey:@"buildingIds"];
        [buildingIds enumerateObjectsUsingBlock:^(NSString *  _Nonnull buildingId, NSUInteger idx, BOOL * _Nonnull stop) {
            NSString * buildSql = @"SELECT * FROM 'dayReport' WHERE analysisDate = ? AND buildingId = ?;";
            FMResultSet* buildingSet = [db executeQuery:buildSql,analysisDate,buildingId];
            NSMutableArray *array = [NSMutableArray array];
            while ([buildingSet next]) {
                YWDailyReport* dailyReport = [self dailyReportWithFMResultSet:buildingSet FMDatabase:db];
                [array addObject:dailyReport];
            }
            [buildingDailyArray addObject:array];
        }];
        [dailyDic setObject:buildingDailyArray forKey:@"buildingDailyArray"];
        [db closeOpenResultSets];
    }];
    return dailyDic;
}

- (YWDailyReport*)dailyReportWithFMResultSet:(FMResultSet*)set FMDatabase:(FMDatabase*)db{
    YWDailyReport * dailyReport = [[YWDailyReport alloc] init];
    dailyReport.buildingId = [set stringForColumn:@"buildingId"];
    dailyReport.analysisDate = [set stringForColumn:@"analysisDate"];
    dailyReport.analysisDateTime = [set longLongIntForColumn:@"analysisDateTime"];
    dailyReport.total = [set intForColumn:@"total"];
    dailyReport.unFinishCount = [set intForColumn:@"unFinishCount"];
    dailyReport.finishCount = [set intForColumn:@"finishCount"];
    dailyReport.tempCount = [set intForColumn:@"tempCount"];
    dailyReport.unDistributeCount = [set intForColumn:@"unDistributeCount"];
    dailyReport.maintenanceTypeId = [set intForColumn:@"maintenanceTypeId"];
    return dailyReport;
}
@end
